﻿<%@ WebHandler Language="C#" Class="ReportAssesmentPositionIDPHandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using Assesment.Library.DataAccess.Administration;
using Assesment.Library.DomainObject.Administration;
using Assesment.Library.DataAccess.Administration.Catalog;
using Assesment.Library.DataAccess.Transaction;
using Assesment.Library.DomainObject.Transaction;

using System.IO;
using OfficeOpenXml;

using Assesment.Library.Enum;
using Assesment.Web.Support;
using Newtonsoft.Json.Linq;
using System.Drawing;
using System.Data;

public class ReportAssesmentPositionIDPHandler : Assesment.Web.BaseHandler
{
    AssesmentPositionIDPRepo AssesmentPositionIDPDal = new AssesmentPositionIDPRepo(ConfigurationManager.ConnectionStrings["AssesmentConn"].ConnectionString);
    //TotalFitRepo TotalFitDal = new TotalFitRepo(ConfigurationManager.ConnectionStrings["AssesmentConn"].ConnectionString);
    //RecomendationRepo RecomendationDal = new RecomendationRepo(ConfigurationManager.ConnectionStrings["AssesmentConn"].ConnectionString);
    
    public override void ProcessRequest(HttpContext context)
    {
        JSONHelper helper = new JSONHelper();

        base.ProcessRequest(context);

        context.Response.Clear();
        context.Response.Buffer = true;

                try
                {
                    int AssesmentIdpID=0;
                    if (context.Request.Params["AssesmentIdpID"] != null)
                        AssesmentIdpID = Convert.ToInt32(context.Request.Params["AssesmentIdpID"].ToString());
                    
                    string NamaKaryawan = string.Empty;
                    string NIK = string.Empty;
                    string Jabatan = string.Empty;
                    string Departemen = string.Empty;
                    string Lokasi = string.Empty;
                    string LokasiKerja = string.Empty;
                    string TglAssesment = string.Empty;
                    string JabatanTujuan = string.Empty;
                    string DepartemenTujuan = string.Empty;
                    string LokasiTujuan = string.Empty;

                    string Rating = string.Empty;
                    
                    DataTable dtAssesmentIDP = AssesmentPositionIDPDal.GetDataTableReport(AssesmentIdpID);
                    if (dtAssesmentIDP.Rows.Count > 0)
                    {
                        NamaKaryawan = dtAssesmentIDP.Rows[0]["NamaKaryawan"].ToString();
                        NIK = dtAssesmentIDP.Rows[0]["NIK"].ToString();
                        Jabatan = dtAssesmentIDP.Rows[0]["PositionName"].ToString();
                        Departemen = dtAssesmentIDP.Rows[0]["DepartmentDesc"].ToString();
                        Lokasi = dtAssesmentIDP.Rows[0]["Location"].ToString();
                        TglAssesment = dtAssesmentIDP.Rows[0]["AssesmentDate"].ToString();

                        JabatanTujuan = dtAssesmentIDP.Rows[0]["PositionAssesment"].ToString();
                        DepartemenTujuan = dtAssesmentIDP.Rows[0]["DepartmentAssesment"].ToString();
                        LokasiTujuan = dtAssesmentIDP.Rows[0]["LocationAssesment"].ToString();
                        
                    }


                    DataTable dtAssesmentIDPDetail = new DataTable();
                    
                    FileInfo newFile = new FileInfo(context.Server.MapPath("~/UploadReport" + @"/"+NIK+"_"+NamaKaryawan+"_IDP.xlsx"));
                    // If any file exists in this directory having name 'Sample1.xlsx', then delete it
                    if (newFile.Exists)
                    {
                        newFile.Delete(); // ensures we create a new workbook
                        newFile = new FileInfo(context.Server.MapPath("~/UploadReport" + @"/" + NIK + "_" + NamaKaryawan + "_IDP.xlsx"));
                    }
                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {
                        // Add a worksheet to the empty workbook
                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Assesment_IDP");
                        worksheet.View.ShowGridLines = false;

                        worksheet.Cells["D4:D11"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E4:K4"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E5:K5"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E6:K6"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E7:K8"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E9:K9"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["K4:K9"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["F4:F9"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["F4:F9"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["I4:I7"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["I4:I7"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                        worksheet.Cells["C3:O3"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["C3:O3"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                                                
                        worksheet.Cells["C12:O12"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["C13:O13"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        
                        worksheet.Column(1).Width = 3;
                        worksheet.Column(2).Width = 2;
                        
                        worksheet.Cells["B1:P1"].Merge = true;
                        worksheet.Cells["B2:P2"].Merge = true;
                        worksheet.Cells["C3:O3"].Merge = true;

                        worksheet.Cells["C3:O3"].Value = "FORM INDIVIDUAL DEVELOPMENT PLAN";
                        worksheet.Cells["C3:O3"].Style.Font.Bold = true;
                        worksheet.Cells["C3"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        worksheet.Row(3).Height = 20;
                        worksheet.Cells["C3"].Style.Font.Size = 14;
                        worksheet.Cells["C3:O3"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        worksheet.Cells["C3:O3"].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        

                        worksheet.Column(5).Width = 20;
                        worksheet.Column(7).Width = 20;
                        worksheet.Column(6).Width = 2;

                        worksheet.Column(8).Width = 20;
                        worksheet.Column(9).Width = 2;
                        worksheet.Column(10).Width = 20;

                        worksheet.Cells["B2:P2"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E4"].Value = "NAMA";
                        worksheet.Cells["F4"].Value = ":";
                        worksheet.Cells["G4"].Value = NamaKaryawan;

                        worksheet.Cells["E5"].Value = "NIK";
                        worksheet.Cells["F5"].Value = ":";
                        worksheet.Cells["G5"].Value = NIK;

                        worksheet.Cells["E6"].Value = "JABATAN";
                        worksheet.Cells["F6"].Value = ":";
                        worksheet.Cells["G6"].Value = Jabatan;

                        worksheet.Cells["E7"].Value = "LEVEL JABATAN";
                        worksheet.Cells["F7"].Value = ":";

                        worksheet.Cells["E8"].Value = "DEPARTEMEN";
                        worksheet.Cells["F8"].Value = ":";
                        worksheet.Cells["G8"].Value = Departemen;

                        worksheet.Cells["E9"].Value = "LOKASI KERJA";
                        worksheet.Cells["F9"].Value = ":";
                        worksheet.Cells["G9"].Value = Lokasi;

                        worksheet.Cells["H4"].Value = "TANGGAL ASSESMENT";
                        worksheet.Cells["I4"].Value = ":";
                        worksheet.Cells["J4"].Value = TglAssesment;

                        worksheet.Cells["H5"].Value = "JABATAN TUJUAN";
                        worksheet.Cells["I5"].Value = ":";
                        worksheet.Cells["J5"].Value = JabatanTujuan;
                            
                        worksheet.Cells["H6"].Value = "LEVEL JABATAN";
                        worksheet.Cells["I6"].Value = ":";
                        

                        worksheet.Cells["H7"].Value = "DEPARTEMEN";
                        worksheet.Cells["I7"].Value = ":";
                        worksheet.Cells["J7"].Value = DepartemenTujuan;

                        worksheet.Cells["C12:E12"].Merge = true;
                        worksheet.Cells["C12:E12"].Value = "DIMENSI KOMPETENSI";
                        worksheet.Cells["C12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["C12"].Style.Font.Size = 14;
                        worksheet.Cells["C12"].Style.Font.Bold = true;

                        worksheet.Cells["F12:G12"].Merge = true;
                        worksheet.Cells["F12"].Value = "LEVEL";
                        worksheet.Cells["F12"].Style.Font.Bold = true;
                        worksheet.Cells["F12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                             
                             
                        worksheet.Cells["H12:J12"].Merge = true;
                        worksheet.Cells["H12:J12"].Value = "GAP KEY BEHAVIOUR";
                        worksheet.Cells["H12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["H12"].Style.Font.Size = 14;
                        worksheet.Cells["H12"].Style.Font.Bold = true;

                        worksheet.Cells["K12"].Value = "DEVELOPMENT ACTIVITY";
                        worksheet.Column(11).Width = 30;
                        worksheet.Cells["K12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                       // worksheet.Cells["K12"].Style.Font.Size = 14;
                        worksheet.Cells["K12"].Style.Font.Bold = true;

                        worksheet.Cells["L12"].Value = "METODE DEVELOPMENT";
                        worksheet.Column(12).Width = 30;
                        worksheet.Cells["L12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["L12"].Style.Font.Size = 14;
                        worksheet.Cells["L12"].Style.Font.Bold = true;

                        worksheet.Cells["M12"].Value = "SUPORTING PEOPLE";
                        worksheet.Column(13).Width = 30;
                        worksheet.Cells["M12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["M12"].Style.Font.Size = 14;
                        worksheet.Cells["M12"].Style.Font.Bold = true;

                        worksheet.Cells["N12:O12"].Merge = true;
                        worksheet.Cells["N12:O12"].Value = "PLAN";
                        worksheet.Cells["N12:O12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        worksheet.Cells["N12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["N12"].Style.Font.Size = 14;
                        worksheet.Cells["N12"].Style.Font.Bold = true;
                        
                        worksheet.Cells["N13"].Value = "START DATE";
                        worksheet.Cells["N13"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["N13"].Style.Font.Size = 14;
                        worksheet.Cells["N13"].Style.Font.Bold = true;
                        
                        worksheet.Cells["O13"].Value = "END DATE";
                        worksheet.Cells["O13"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //worksheet.Cells["O13"].Style.Font.Size = 14;
                        worksheet.Cells["O13"].Style.Font.Bold = true;

                        worksheet.Cells["C12:O12"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        worksheet.Cells["C12:O12"].Style.Fill.BackgroundColor.SetColor(Color.RoyalBlue);
                        worksheet.Cells["C13:O13"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        worksheet.Cells["C13:O13"].Style.Fill.BackgroundColor.SetColor(Color.RoyalBlue);
                        
                        worksheet.Column(14).Width = 15;
                        worksheet.Column(15).Width = 15;
                        
                        worksheet.Cells["C14:O14"].Merge = true;

                        worksheet.Cells["C14"].Value = "CORE COMPETENCY";
                        worksheet.Cells["C14:O14"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        worksheet.Cells["C14:O14"].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        worksheet.Cells["C14:O14"].Style.Font.Bold = true;

                        //dtAssesmentIDPDetail = AssesmentPositionIDPDal.GetDataTableReport_Detail(AssesmentIdpID, "CUSTOMER FOCUS");
                        int ColKeyBehaviour = 15;
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "CUSTOMER FOCUS", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "INTEGRITY", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "TEAM WORK", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "CONTINOUS LEARNING", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "WORK EXCELLENCE", worksheet, ColKeyBehaviour);

                        //ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C"+ColKeyBehaviour.ToString()].Value = "MANAGERIAL COMPETENCY";
                        worksheet.Cells["C"+ColKeyBehaviour.ToString()+":O"+ColKeyBehaviour.ToString()].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":O" + ColKeyBehaviour.ToString()].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":O" + ColKeyBehaviour.ToString()].Style.Font.Bold = true;

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "FINANCIAL", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "PARTNERSHIP", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "LEADERSHIP", worksheet, ColKeyBehaviour);
                        ColKeyBehaviour = LoadValue(AssesmentIdpID, "RESOURCE MANAGEMENT", worksheet, ColKeyBehaviour);

                        worksheet.Cells["C3:C" + (ColKeyBehaviour-1).ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        //worksheet.Cells["C" + ColKeyBehaviour.ToString()+":O"+ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["O12:O" + (ColKeyBehaviour-1).ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        
                        ColKeyBehaviour=ColKeyBehaviour+2;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "Keterangan";
                        worksheet.Cells["C" + ColKeyBehaviour].Style.Font.Bold = true;

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "* Key behaviour yang muncul dalam form IDP adalah prilaku yang di harapkan pada jabatan yang di tuju, namun belum muncul dalam proses assesment";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "* key behaviour yang muncul dalam form IDP menjadi fokus pengembangan bagi individu tsb.";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "* Atasan /  calon atasan bersama-sama dengan tim HRD menentukan aktivitas pengembangan (development activity) bagi individu";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "* Aktivitas pengembangan (development activity) adalah skill, pengetahuan yang akan menjadi tema pengembangan individu dan mengacu pada key behaviour.";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "contoh : Dimesi kompetensi CUSTOMER FOKUS dengan KEY BEHAVIOUR : secara berkelanjutan mencari tahu kebutuhan pelanggan ";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "DEVELOPMENT ACTIVITY : Kemampuan dalam pembuatan survey kepuasan pelanggan dan penyusunan report";


                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "* METODE DEVELOPMENT adalah cara yang akan di gunakan untuk menginjeksi skill dan pengetahuan kepada individu";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "* SUPPORTING PEOPLE : adalah PIC yang ditunjuk untuk menjadi mentor bagi individu terkait, SUPPORTING PEOPLE tidak harus selalu atasan langsung";

                        ColKeyBehaviour = ColKeyBehaviour + 4;
                        worksheet.Cells["C"+ColKeyBehaviour.ToString()+":G"+ColKeyBehaviour.ToString()].Merge = true;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "TANDA TANGAN";
                        worksheet.Cells["C" + ColKeyBehaviour].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        worksheet.Cells["C" + ColKeyBehaviour].Style.Font.Bold = true;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Fill.BackgroundColor.SetColor(Color.LightGray);

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "ATASAN";
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":D" + ColKeyBehaviour.ToString()].Merge = true;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":D" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["C" + ColKeyBehaviour].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        
                        worksheet.Cells["E" + ColKeyBehaviour].Value = "HRD";
                        worksheet.Cells["E" + ColKeyBehaviour].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["F" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Merge = true;
                        worksheet.Cells["F" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E" + ColKeyBehaviour].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        
                        worksheet.Cells["F" + ColKeyBehaviour].Value = "PESERTA ASSESMENT";
                        worksheet.Cells["F" + ColKeyBehaviour].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        ColKeyBehaviour = ColKeyBehaviour + 7;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "Nama :";
                        worksheet.Cells["E" + ColKeyBehaviour].Value = "Nama :";
                        worksheet.Cells["F" + ColKeyBehaviour].Value = "Nama :";

                        ColKeyBehaviour = ColKeyBehaviour + 1;
                        worksheet.Cells["C" + ColKeyBehaviour].Value = "Tanggal :";
                        worksheet.Cells["E" + ColKeyBehaviour].Value = "Tanggal :";
                        worksheet.Cells["F" + ColKeyBehaviour].Value = "Tanggal :";

                        worksheet.Cells["C"+(ColKeyBehaviour-9).ToString()+":C"+ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["G" + (ColKeyBehaviour - 9).ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["D" + (ColKeyBehaviour - 9).ToString() + ":D" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["E" + (ColKeyBehaviour - 9).ToString() + ":E" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                        ColKeyBehaviour = ColKeyBehaviour + 3;
                        worksheet.Cells["B2:B"+ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["B"+ColKeyBehaviour.ToString()+":P" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        worksheet.Cells["P2"+ ":P" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        
                        //int ColKeyBehaviour = 15;
                        //if (dtAssesmentIDPDetail != null)
                        //{
                        //    if (dtAssesmentIDPDetail.Rows.Count > 0)
                        //    {
                        //        worksheet.Cells["C" + ColKeyBehaviour.ToString()].Value = dtAssesmentIDPDetail.Rows[0]["CompetncyDesc"].ToString().ToUpper();
                        //        worksheet.Cells["K" + ColKeyBehaviour.ToString()].Value = dtAssesmentIDPDetail.Rows[0]["DevelopmentActivity"].ToString().ToUpper();
                        //        foreach (DataRow row in dtAssesmentIDPDetail.Rows)
                        //        {
                        //            worksheet.Cells["H" + ColKeyBehaviour.ToString() + ":J" + ColKeyBehaviour.ToString()].Merge = true;
                        //            worksheet.Cells["H" + ColKeyBehaviour.ToString()].Value = row["KeyBehaviourName"].ToString();


                        //            worksheet.Cells["L" + ColKeyBehaviour.ToString()].Value = row["MethodName"].ToString();
                        //            worksheet.Cells["M" + ColKeyBehaviour.ToString()].Value = row["SupportingPeople"].ToString();
                        //            worksheet.Cells["N" + ColKeyBehaviour.ToString()].Value = row["StartDate"].ToString();
                        //            worksheet.Cells["O" + ColKeyBehaviour.ToString()].Value = row["EndDate"].ToString();
                                    
                        //            ColKeyBehaviour++;
                        //            //ColCompetency++;
                        //        }
                        //    }
                        //}
                        
                        package.Save();

                        string filename = context.Server.MapPath("~/UploadReport" + @"/" + NIK + "_" + NamaKaryawan + "_IDP.xlsx");
                        System.IO.FileInfo fileInfo = new System.IO.FileInfo(filename);

                        if (fileInfo.Exists)
                        {
                            context.Response.ContentType = "application/vnd.ms-excel";
                            context.Response.Clear();
                            context.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + fileInfo.Name + "\"");
                            context.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                            context.Response.ContentType = "application/octet-stream";
                            context.Response.TransmitFile(fileInfo.FullName);
                            context.Response.Flush();

                            //helper.IsSuccess = true;
                            //helper.Message = "sukses";
                        }
                    }
                }
                catch (Exception ex)
                {
                    context.Response.ContentType = "text/plain";
                    context.Response.Write(ex.Message);
                }
                finally
                {
                    context.Response.End();
                }
                




          



        //context.Response.ContentType = "text/plain";
        //context.Response.Write("Hello World");
    }

    private int LoadValue(int AssesmentIdpID, string CompetncyDesc, ExcelWorksheet worksheet, int ColKeyBehaviour)
    {
        DataTable dtAssesmentIDPDetail = AssesmentPositionIDPDal.GetDataTableReport_Detail(AssesmentIdpID, CompetncyDesc);
         
        if (dtAssesmentIDPDetail != null)
        {
            if (dtAssesmentIDPDetail.Rows.Count > 0)
            {
                worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":E" + ColKeyBehaviour.ToString()].Merge = true;
                worksheet.Cells["C" + ColKeyBehaviour.ToString()].Value = dtAssesmentIDPDetail.Rows[0]["CompetncyDesc"].ToString().ToUpper();
                worksheet.Cells["K" + ColKeyBehaviour.ToString()].Value = dtAssesmentIDPDetail.Rows[0]["DevelopmentActivity"].ToString().ToUpper();
                worksheet.Cells["G" + ColKeyBehaviour.ToString()].Value = dtAssesmentIDPDetail.Rows[0]["Level"].ToString();
                worksheet.Cells["G" + ColKeyBehaviour.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                foreach (DataRow row in dtAssesmentIDPDetail.Rows)
                {
                   
                    
                    worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":E" + ColKeyBehaviour.ToString()].Merge = true;
                    
                    worksheet.Cells["H" + ColKeyBehaviour.ToString() + ":J" + ColKeyBehaviour.ToString()].Merge = true;
                    worksheet.Cells["H" + ColKeyBehaviour.ToString()].Value = row["KeyBehaviourName"].ToString();
                    worksheet.Cells["H" + ColKeyBehaviour.ToString()].Style.WrapText = true;
                    
                    worksheet.Cells["H" + ColKeyBehaviour.ToString() + ":J" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["H" + ColKeyBehaviour.ToString() + ":J" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["H" + ColKeyBehaviour.ToString() + ":J" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    
                    worksheet.Row(ColKeyBehaviour).Height = 30;

                    worksheet.Cells["L" + ColKeyBehaviour.ToString()].Value = row["MethodName"].ToString();
                    
                    worksheet.Cells["L" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["L" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["L" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    
                    worksheet.Cells["M" + ColKeyBehaviour.ToString()].Value = row["SupportingPeople"].ToString();

                    worksheet.Cells["M" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["M" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["M" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    
                    worksheet.Cells["N" + ColKeyBehaviour.ToString()].Value = row["StartDate"].ToString();

                    worksheet.Cells["N" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["N" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["N" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    
                    worksheet.Cells["O" + ColKeyBehaviour.ToString()].Value = row["EndDate"].ToString();

                    worksheet.Cells["O" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["O" + ColKeyBehaviour.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    worksheet.Cells["O" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                    worksheet.Cells["G" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    

                    ColKeyBehaviour++;
                    //ColCompetency++;
                }

                //worksheet.Cells["K" + ColKeyBehaviour.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells["K" + ColKeyBehaviour.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                //worksheet.Cells["K" + ColKeyBehaviour.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                worksheet.Cells["C" + ColKeyBehaviour.ToString() + ":G" + ColKeyBehaviour.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            }
        }
        return ColKeyBehaviour;
       // ColKeyBehaviour;
        
    }


    
 
    public bool IsReusable {
        get {
            return false;
        }
    }

}